import json
import re
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import create_engine

select_sql = """SELECT
	u.username AS 登录账户,	u.real_name AS 姓名,
    u.sno AS 准考证号,
    u.identity AS 身份证号,
	u.academy AS 考场,
    u.exam_location AS 考试地点,
	ap.total_score AS  满分,
    {total},{query_str}
FROM
	(SELECT
			q.user_id,
			{sub_query_str}
		FROM
			answer_question_{exam_id} q
		GROUP BY
			q.user_id) AS a,
    (SELECT total_score, user_id
      FROM answer_paper p
      WHERE exam_id = '{exam_id}') AS ap,
	`user` AS u
WHERE
a.user_id=u.id AND
ap.user_id = u.id AND
u.role='stu'
ORDER BY u.id;
"""

select_dt_sql = """
SELECT
    u.username AS 登录账户,
    ap.gets AS 会计电算化
FROM answer_topic as ap
JOIN user as u on ap.user_id = u.id
WHERE topic_id in (
        SELECT id
        FROM topic_in_paper
        WHERE topic_type = 'cz'
            and paper_id in (
                Select paper_id
                from exam
                WHERE id = {exam_id}
            )
    ) AND u.role = 'stu'
ORDER BY
    u.id
"""

#组装获取主题题目分数的sql
def return_topic_score(engine: sa.engine.Engine, paper_id: int):
    sql = 'SELECT * FROM topic_in_paper WHERE paper_id={}'.format(paper_id)
    query = []
    sub_query = []
    total = []
    try:
        with engine.connect() as conn, conn.begin():
            data = pd.read_sql_query(sql, conn)
            for index, row in data.iterrows():
                name = ''.join(
                    re.findall(
                        '[\u4e00-\u9fa5a-zA-Z0-9]+', 
                        ''.join(json.loads(row.topic_name)), 
                        re.S))
                query.append(
                    'a.topic{index} AS {name}'.format(index=index, name=name))
                sub_query.append(
                    'SUM(IF(q.topic_id IN({id}), gets, 0)) AS topic{index}'.format(id=row.id, index=index))
                total.append('a.topic{index}'.format(index=index))
        return [',\n'.join(query), ',\n'.join(sub_query), '{}  AS 个人总分'.format('+'.join(total))]
    except Exception as e:
        print(e)
    finally:
        conn.close()

#获取主题及电算化的分数
def select_user_score(engine: sa.engine.Engine, exam_id: int, query_str: str, sub_query_str: str, total: str, ip: str):
    try:
        with engine.connect() as conn, conn.begin():
            data = pd.read_sql_query(select_sql.format(
                exam_id=exam_id, query_str=query_str, sub_query_str=sub_query_str, total=total), conn, index_col='登录账户').sort_index(ascending=False)
            ds_data = pd.read_sql_query(
                select_dt_sql.format(exam_id=exam_id), 
                conn, index_col='登录账户')
            data['会计电算化'] = ds_data['会计电算化']
            data.to_excel('{ip}_{exam_id}.xlsx'.format(exam_id=exam_id, ip=ip))
            # ds_data.to_excel('{ip}_{exam_id}_电算化.xlsx'.format(exam_id=exam_id,ip=ip))    
            return [data, ds_data]
    except Exception as e:
        print(e)
    finally:
        conn.close()

#选取当前导出的考试
def input_select_exam(engine: sa.engine.Engine):
    sql = 'SELECT id,team_id,paper_id,exam_name,create_at FROM exam'
    try:
        with engine.connect() as conn, conn.begin():
            data = pd.read_sql_query(sql, conn, index_col='id')
            print(data)
            exam_id = int(input('选择需要导出考试序号：'))
            return [exam_id, data.loc[exam_id, 'paper_id']]
    except Exception as e:
        print(e)
    finally:
        conn.close()

def main():
    condition = 'y'
    while condition == 'y':
        ip = input('输入服务器IP地址：')
        engine = create_engine(
        'mysql+pymysql://root:qq^ju8li7.%mm@{}:3306/bd-exam'.format(ip))
        [exam_id, paper_id] = input_select_exam(engine)
        [query_str, sub_query_str, total] = return_topic_score(engine, paper_id)
        data = select_user_score(engine, exam_id, query_str,
                             sub_query_str, total, ip)
        condition = input('是否继续？(y/n)')

if __name__ == "__main__":
    main()
